package com.cn.wanxi.dao.impl;

import com.cn.wanxi.dao.UserDao;
import com.cn.wanxi.model.PageModel;
import com.cn.wanxi.model.UserModel;
import com.cn.wanxi.util.JDBC;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * 1：封装sql语句
 * 2.得到注册结果：成功与失败
 * 3。返回结果数据
 */
public class UserImpl implements UserDao {
    @Override
    public int register(UserModel userModel) {
        String sql = "INSERT INTO `user` \n" +
                "(`username`, " +
                "`password`, " +
                "`phone`, " +
                "`sex`, " +
                "`birthday`, " +
                "`hobby`," +
                "create_time,email)\n" +
                " VALUES ('" + userModel.getUsername() + "', " +
                "'" + userModel.getPassword() + "', " +
                "'" + userModel.getPhone() + "', " +
                "'" + userModel.getSex() + "', " +
                "'" + userModel.getBirthday() + "', " +
                "'" + userModel.getHobby() + "'," +
                "now(),'')";//新增用户信息的数据


        return JDBC.excute(sql);
    }

    @Override
    public UserModel findByUsername(String username) {
        String sql = "select * from user where username='" + username + "'";
        ResultSet resultSet = JDBC.query(sql);//得到查询的结果
        UserModel userModel = null;
        try {
            while (resultSet.next()) {
                userModel = new UserModel();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return userModel;
    }

    @Override
    public UserModel findByUsernameAndPassword(UserModel userModel) {
        String sql = "select * from user " +
                "where username='" + userModel.getUsername() + "' and " +
                "password='" + userModel.getPassword() + "'";
        ResultSet resultSet = JDBC.query(sql);//得到查询的结果
        UserModel model = null;
        try {
            while (resultSet.next()) {
                model = new UserModel();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;
    }

    @Override
    public List<UserModel> findAll(UserModel userModel) {
        String sql = "select * from user " +
                "where true";
        if (userModel.getUsername() != null && !"".equals(userModel.getUsername())) {
            sql += " and username like '%" + userModel.getUsername() + "%' ";
        }
        if (userModel.getPhone() != null && !"".equals(userModel.getPhone())) {
            sql += "   and phone like '%" + userModel.getPhone() + "%'";
        }
        if (userModel.getEmail() != null && !"".equals(userModel.getEmail())) {
            sql += "   and email like '%" + userModel.getEmail() + "%'";
        }
        if (userModel.getSex() != null && !"全部".equals(userModel.getSex())) {
            sql += "   and sex ='" + userModel.getSex() + "'";
        }
        if (!"".equals(userModel.getHobby())) {
            String[] hobbys = userModel.getHobby().split(",");
            sql += " and (";
            for (int i = 0; i < hobbys.length; i++) {
                if (i == 0) {
                    sql += "  FIND_IN_SET('" + hobbys[i] + "',hobby)";
                } else {
                    sql += " or FIND_IN_SET('" + hobbys[i] + "',hobby)";
                }
            }
            sql += ")";
        }
        if ("".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql += " and birthday <='" + userModel.getEndBirthday() + "'";
        }
        if (!"".equals(userModel.getStartBirthday()) && "".equals(userModel.getEndBirthday())) {
            sql += " and birthday >='" + userModel.getStartBirthday() + "'";
        }
        if (!"".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql += " and birthday between '" + userModel.getStartBirthday() + "' and '" + userModel.getEndBirthday() + "'";
        }
        if (userModel.getEnable() != -1) {
            if (userModel.getEnable() == 1) {
                sql += " and enable=1";
            } else {
                sql += " and enable!=1";
            }
        }
        sql += " order by id desc";
//        传值：传2个参数,用对象分页对象
        int startSize = (userModel.getPageModel().getCurPage() - 1) * userModel.getPageModel().getPageSize();
        sql += " limit  " + startSize + "," +
                "" + userModel.getPageModel().getPageSize() + "";
        ResultSet resultSet = JDBC.query(sql);//得到查询的结果

        return setResultData(resultSet);
    }

    @Override
    public int del(Integer id) {
        String sql = "delete from user where id=" + id;

        return JDBC.excute(sql);
    }

    @Override
    public UserModel findById(Integer id) {
        String sql = "select * from user where id=" + id;
        ResultSet resultSet = JDBC.query(sql);
        List<UserModel> list = setResultData(resultSet);
        return list.size() > 0 ? list.get(0) : null;
    }

    @Override
    public int edit(UserModel userModel) {
        String sql = "update user set username='" + userModel.getUsername() + "' ,phone='" + userModel.getPhone() + "'" +
                " where id=" + userModel.getId();
        return JDBC.excute(sql);
    }

    @Override
    public UserModel findByUsernameForEdit(UserModel userModel) {
        String sql = "select * from user where username='" + userModel.getUsername() + "' and id!=" + userModel.getId();
        ResultSet resultSet = JDBC.query(sql);
        List<UserModel> list = setResultData(resultSet);
        return list.size() > 0 ? list.get(0) : null;
    }

    @Override
    public int getCount(UserModel userModel) {
        String sql = "select count(*) count from user " +
                "where true";
        if (userModel.getUsername() != null && !"".equals(userModel.getUsername())) {
            sql += " and username like '%" + userModel.getUsername() + "%' ";
        }
        if (userModel.getPhone() != null && !"".equals(userModel.getPhone())) {
            sql += "   and phone like '%" + userModel.getPhone() + "%'";
        }
        if (userModel.getEmail() != null && !"".equals(userModel.getEmail())) {
            sql += "   and email like '%" + userModel.getEmail() + "%'";
        }
        if (userModel.getSex() != null && !"全部".equals(userModel.getSex())) {
            sql += "   and sex ='" + userModel.getSex() + "'";
        }
        if (!"".equals(userModel.getHobby())) {
            String[] hobbys = userModel.getHobby().split(",");
            sql += " and (";
            for (int i = 0; i < hobbys.length; i++) {
                if (i == 0) {
                    sql += "  FIND_IN_SET('" + hobbys[i] + "',hobby)";
                } else {
                    sql += " or FIND_IN_SET('" + hobbys[i] + "',hobby)";
                }
            }
            sql += ")";
        }
        if ("".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql += " and birthday <='" + userModel.getEndBirthday() + "'";
        }
        if (!"".equals(userModel.getStartBirthday()) && "".equals(userModel.getEndBirthday())) {
            sql += " and birthday >='" + userModel.getStartBirthday() + "'";
        }
        if (!"".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql += " and birthday between '" + userModel.getStartBirthday() + "' and '" + userModel.getEndBirthday() + "'";
        }
        if (userModel.getEnable() != -1) {
            if (userModel.getEnable() == 1) {
                sql += " and enable=1";
            } else {
                sql += " and enable!=1";
            }
        }
        sql += " order by id desc";
        ResultSet resultSet = JDBC.query(sql);
        int count = 0;
        try {
            while (resultSet.next()) {
                count = resultSet.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    private List<UserModel> setResultData(ResultSet resultSet) {
        List<UserModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                UserModel model = new UserModel();
                model.setId(resultSet.getInt("id"));
                model.setUsername(resultSet.getString("username"));
                model.setPhone(resultSet.getString("phone"));
                model.setEmail(resultSet.getString("email"));
                model.setHobby(resultSet.getString("hobby"));
                model.setSex(resultSet.getString("sex"));
                model.setEnable(resultSet.getInt("enable"));
                model.setBirthday(resultSet.getString("birthday"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

}
